CODEWITHSUNDEEP
sql-server-2005
Abhishek
Abhishek

Reputation: 1

Putting getdate()'s data into a string

In sqlserver 2005, I am using this query:

select getdate() or print getdate()

I want to use this returned date as a string to get access to:

  1. the month
  2. The year(last two digits)
  3. date (eg: 01)
  4. time (eg: 5:12)

I want to concatenate all the data. All that should be in string format.

Upvotes: 0

Views: 96

Answers (3)

beer_monk
beer_monk

Reputation: 956

If you already have all the pieces as strings you can concatenate them and use CONVERT()

declare @my_year varchar(4)
declare @my_month varchar(4)
declare @my_day varchar(4)
declare @my_hour varchar(4)
declare @my_minute varchar(4)
declare @my_second varchar(4)

set @my_year = '1900'
set @my_month = '05'
set @my_day = '13'
set @my_hour = '05'
set @my_minute = '12'
set @my_second = '00'

select CONVERT(datetime,@my_year + 
                    @my_month + 
                    @my_day + 
                    ' ' + 
                    @my_hour + ':' + 
                    @my_minute + ':' + 
                    @my_second)
)

Upvotes: 0

grimorde
grimorde

Reputation: 265

Can you use:

SELECT CONVERT(VARCHAR(19), GETDATE(), 120)

to get:

2011-05-11 15:02:36

and manipulate that?

Upvotes: 0

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

First of all: ALL formatting should be done on the client.
Now that we got that out of the way look into CONVERT and DATEPART functions.
CONVERT offers lots of styles you can use, while DATEPART returns part of the date as integer which you can convert to varchar.

Just note that there are a lot of functions like these on the web already so searching for some wouldn't be a bad idea.

Upvotes: 3

Related Questions