Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert date in yyyy-mm format


Insert date in yyyy-mm format

Author
Message
golansimani
golansimani
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
Hi,

I'm trying to insert random date in yyyy-mm format to have expiry Credit card date column.
Haw can I do it? is it possible at all?
I know that I can sore the full (yyyy-mm-dd) and then to select with: convert(char(7),column_a,121)
but this is not the way I'm looking for .

Thanks
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 25280
This might be what you are looking for:


SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]

Result:
2013/09
]



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
golansimani
golansimani
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
If I want to select it's working but as I mentioned I'm trying to insert and because my column is a 'date type' when I'm using CONVERT(VARCHAR(7), GETDATE(), 111)-- AS [YYYY/MM]
I'm getting this error message:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.


Do u have any suggestions?

Thanks for trying to help
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4468 Visits: 9836
In order to insert data into a date data type - you need a valid date or string that can be converted into a date.

If you are looking for random dates - then wouldn't you want random days also? Or are you looking for random months only? If random months only, then just pick a day - either the 1st or 15th would be my recommendation.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

golansimani
golansimani
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
I don't have a problem to get a random month or a day I just using this :

UPDATE table_a
SET column_a= DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')

But the column that I'm trying to populate is a 'credit card expiry date' and it need to be '2015/01' (yyyy/mm).
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16680 Visits: 17038
golansimani (9/23/2013)
I don't have a problem to get a random month or a day I just using this :

UPDATE table_a
SET column_a= DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')

But the column that I'm trying to populate is a 'credit card expiry date' and it need to be '2015/01' (yyyy/mm).



Since your target column is not a datetime you can't use the results of DATEADD. That function returns a datetime. You would have to kludge this a bit more.


SET column_a = cast(year(DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')) as char(4))
+ '/'
+ right('0' + cast(month(DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')) as varchar(2)), 2)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
golansimani
golansimani
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
Its not working , I got this message :
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16680 Visits: 17038
golansimani (9/23/2013)
Its not working , I got this message :
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.


What is the datatype of column_a? I suspect that column is actually a datetime based on your message.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
golansimani
golansimani
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
Its a Date Data Type
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16680 Visits: 17038
golansimani (9/23/2013)
Its a Date Data Type


Then the code you posted should work fine.


SET column_a= DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')



Remember there is not such thing as a format for date columns.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search