Professional Web Applications Themes

construct a date string in TSQL - Microsoft SQL / MS SQL Server

How do you build a date in SQL Server sp? I need to perform a Month-to-date calculation so given a date, I need to build a date which is the first day of that month: select datepart(year,'8/12/2003') + datepart(month,'8/12/2003') + '01' I have tried to do this several ways, but sql always ADDS by numbers, instead of concatenating them. In VB we can use the & operator to be expliciting about wanting a concat. and not an addition. How to do it sql? Cheers, Max Hodges www.maxconsulting.us...

  1. #1

    Default construct a date string in TSQL

    How do you build a date in SQL Server sp?
    I need to perform a Month-to-date calculation so given a date, I need
    to build a date which is the first day of that month:

    select datepart(year,'8/12/2003') + datepart(month,'8/12/2003') + '01'

    I have tried to do this several ways, but sql always ADDS by numbers,
    instead of concatenating them. In VB we can use the & operator to be
    expliciting about wanting a concat. and not an addition. How to do it
    sql?

    Cheers,
    Max Hodges
    www.maxconsulting.us
    maxhodges Guest

  2. #2

    Default construct a date string in TSQL

    you can create a function with following string to return
    a first day of the month !

    select convert(char(4),datepart(year,'8/12/2003')) + '/' +
    convert(varchar(2),datepart(month,'8/12/2003')) + '/'
    + '01'

    instead of '8/12/2003' say getdate() to get first day of
    current month.

    Ram.
     
    date, I need 
    (month,'8/12/2003') + '01' 
    by numbers, 
    operator to be 
    How to do it 
    Ram Guest

  3. #3

    Default RE: construct a date string in TSQL

    Try a double Cast;

    Declare m int
    set m = 0
    while m <= 12
    begin
    select cast(cast(datepart(yyyy,getdate()) as varchar(5))
    +'-'+
    cast(dateadd(mm,m,datepart(mm,getdate())) as varchar(5))
    +'-'+
    cast(datepart(dd,0) as varchar(5))
    as datetime)

    set m = m + 1
    end

    create the complete date string as a varchar then cast it as a Datetime, if
    you do date add or try to compile it as an int it all becomes additive and
    you don't get the datetime imcrements your looking for.

    Hope this helps

    Art Guest

  4. #4

    Default Re: construct a date string in TSQL

    Thanks guys, that was more tricky than one thinks it should be.

    For future searchers, I used this:

    set LogDate = '8/8/2003'
    set StartDate = CAST(DATEPART(YYYY, LogDate) as char(4)) + '-'
    + RIGHT(CAST(100+Datepart(mm,LogDate) as char(3)),2) + '-'
    + RIGHT(cast(100+1 as char(3)),2)


    com (maxhodges) wrote in message news:<google.com>... 
    maxhodges Guest

  5. #5

    Default Re: construct a date string in TSQL

    Max,

    to get the date of first day of current month
    SELECT dateadd(mm,datepart(mm,getdate())-1,'1/1/'+ datename(yy,getdate()))

    to get the date of first day of a given date
    SELECT dateadd(mm,datepart(mm,'8/12/2003')-1,'1/1/'+
    datename(yy,'8/12/2003'))

    Thanks,
    Praveen Maddali
    MCDBA, MCSD

    "maxhodges" <com> wrote in message
    news:google.com... 


    praveen Guest

Similar Threads

  1. How to convert a date string to a Date object
    By kyalong in forum Macromedia Flex General Discussion
    Replies: 6
    Last Post: June 17th, 06:42 AM
  2. Apostrophe sign in TSQL string
    By Michal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 30th, 07:25 AM
  3. String to Date
    By Daniel Bass in forum ASP.NET General
    Replies: 1
    Last Post: July 22nd, 10:54 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139